package com.situ.company.dao;

import com.situ.company.entity.Dept;
import com.situ.company.entity.Employee;
import com.situ.company.util.DBUtil;
import com.situ.company.util.StringUtil;

import java.util.List;

public class DeptDao {
    public List<Dept> all(){
        String sql ="select * from dept";
        return DBUtil.executeDQL(sql, Dept.class);
    }

    public void delete(Integer id) {
        String sql ="delete from dept where id =?";
        DBUtil.executeDML(sql,id);
    }

    public List<Dept> select(Integer page, Integer limit, String name, String tel) {
        String sql ="select * from dept ";
        if(StringUtil.isNotBlank(name)&&StringUtil.isNotBlank(tel)) {
            sql += " where instr(name,?)>0 and instr(tel,?)>0";
            sql += " limit ?,?";
            return DBUtil.executeDQL(sql, Dept.class, name,tel,(page - 1) * limit, limit);
        }
        if(StringUtil.isNotBlank(tel)){
            sql +=" where instr(tel,?)>0 limit ?,?";
            return DBUtil.executeDQL(sql, Dept.class, tel,(page - 1) * limit, limit);
        }
        if(StringUtil.isNotBlank(name)){
            sql +=" where instr(name,?)>0 limit ?,?";
            return DBUtil.executeDQL(sql, Dept.class, name,(page - 1) * limit, limit);
        }
        sql += " limit ?,?";
        return DBUtil.executeDQL(sql, Dept.class, (page - 1) * limit, limit);
    }
    public Long countNumber(Integer id) {
      String sql =  "SELECT  COUNT(1) as 'number' FROM employee LEFT JOIN dept ON dept.id = employee.dept_id WHERE status = 0 and dept_id = ? GROUP BY dept_id";
        return DBUtil.executeCount(sql,id);
    }
        public Long count(String name, String tel) {
        String sql ="select count(1) from dept";
        if(StringUtil.isNotBlank(name) && StringUtil.isNotBlank(tel)) {
            sql += " where instr(name,?)>0 and instr(tel,?)>0";

            return DBUtil.executeCount(sql, name,tel);
        }
        if(StringUtil.isNotBlank(name)){
            sql += " where instr(name,?)>0 ";
            return DBUtil.executeCount(sql, name);
        }
        if(StringUtil.isNotBlank(tel)){
            sql += " where instr(tel,?)>0 ";
            return DBUtil.executeCount(sql, tel);
        }
        return DBUtil.executeCount(sql);
    }

    public void insert(Dept d) {
        String sql ="insert into dept (name,number,tel) values(?,?,?)";
        DBUtil.executeDML(sql,d.getName(),d.getNumber(),d.getTel());
    }

    public void update(Dept d) {
        String sql ="update dept set name = ?,number = ?,tel = ? where id =?";
        DBUtil.executeDML(sql,d.getName(),d.getNumber(),d.getTel(),d.getId());
    }

    public List<Dept> edit(Integer id) {
        String sql ="select * from dept where id = ?";
        return DBUtil.executeDQL(sql,Dept.class,id);
    }
        //根据部门id查这个部门有没有员工
    public List<Employee> check(Integer id){
        String sql ="select * from employee where dept_id =?";
        return  DBUtil.executeDQL(sql, Employee.class,id);
    }

}
